﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using NHibernate.Criterion;
using NHibernate;

namespace NLBS.Data
{
    public partial class NHibernateDataProvider : DbProvider
    {
        #region Article

        public override IPagedCollection<Article> GetRssArticleByPage(int pageSize)
        {
            IList<Article> list = CurrentSession.CreateCriteria(typeof(Article))
                .SetMaxResults(10)
                .Add(Expression.Eq("EnableInRss", true))
                .List<Article>();
              IPagedCollection<Article> lists = new PagedCollection<Article>(list);
                lists.MaxItems =Convert.ToInt32(
                    CurrentSession.CreateQuery("SELECT COUNT(*) from Article as art where art.EnableInRss=1").UniqueResult());
                return lists;
        }

        public override IPagedCollection<Article> GetAllArticleByPage(int startIndex, int pageSize)
        {
            IList<Article> list = CurrentSession.CreateCriteria(typeof(Article))
            .SetFirstResult(startIndex * pageSize)
            .SetMaxResults(pageSize)
            .List<Article>();
            IPagedCollection<Article> lists = new PagedCollection<Article>(list);
            lists.MaxItems = Convert.ToInt32(
            CurrentSession.CreateQuery("SELECT COUNT(*) from Article").UniqueResult());
            return lists;
        }

        public DbParameter CreateParameter(string paramsName,DbType dbtype,object objvalue)
        {
            SqlParameter objParam = new SqlParameter("@"+paramsName, objvalue);
            objParam.DbType = dbtype;
            return objParam;
        }

        public override IPagedCollection<Article> GetCatArticleByPage(int startIndex, int pageSize, int catID)
        {
            IList<Article> list = CurrentSession.CreateCriteria(typeof(Article))
            .SetFirstResult(startIndex * pageSize)
            .SetMaxResults(pageSize)
            .Add(Expression.Eq("CategoryID", catID))
            .List<Article>();
            IPagedCollection<Article> lists = new PagedCollection<Article>(list);
            lists.MaxItems = Convert.ToInt32(
                CurrentSession.CreateQuery("SELECT COUNT(*) from Article as art WHERE art.CategoryID=:catID")
                .SetInt32("catID", catID).UniqueResult());


            return lists;
        }

        public override IPagedCollection<Article> GetSelectedArticleByPage(int startIndex, int pageSize)
        {
            IList<Article> list = CurrentSession.CreateCriteria(typeof(Article))
            .SetFirstResult(startIndex * pageSize)
            .SetMaxResults(pageSize)
            .Add(Expression.Eq("Selected", true))
            .List<Article>();
            IPagedCollection<Article> lists = new PagedCollection<Article>(list);
            lists.MaxItems = Convert.ToInt32(
                CurrentSession.CreateQuery("SELECT COUNT(*) from Article as art WHERE art.Selected=:selected")
                .SetBoolean("selected", true).UniqueResult());
          
            return lists;
        }

        public override IList<Article> GetTopArticle(int intCount)
        {
            
            string sqlWHERE = string.Empty;
            if (intCount < 10)
            {
                intCount = 10;
            }
            foreach (Category item in this.GetCategory())
            {
                if (item.Hidden) sqlWHERE += " AND log_catid<>" + item.CategoryID;
            }
            List<string> intList = new List<string>();
            DbCommand dbHiddenCommand = db.GetSqlStringCommand("SELECT TOP " + intCount + " log_id FROM [blog_Article] WHERE log_mode=1 " + sqlWHERE + " ORDER BY log_postTime DESC");
            using (IDataReader dataReader = db.ExecuteReader(dbHiddenCommand))
            {
                while (dataReader.Read())
                {
                    //if (!intList.Contains(Convert.ToInt32(dataReader["log_id"])))
                        intList.Add(Convert.ToString(dataReader["log_id"]));
                }
            }
            DbCommand dbAllCommand = db.GetSqlStringCommand("SELECT TOP " + intCount + " log_id FROM [blog_Article] ORDER BY log_postTime DESC");
            using (IDataReader dataReader = db.ExecuteReader(dbAllCommand))
            {
                while (dataReader.Read())
                {
                    //if (!intList.Contains(Convert.ToInt32(dataReader["log_id"])))
                    intList.Add(Convert.ToString(dataReader["log_id"]));
                }
            }
            IList<Article> lists = new List<Article>();
            if (intList.Count == 0)
            {
                return lists;
            }
           
            intList.Sort();
            string arrItem = intList[0];
            for (int i = 1; i < intList.Count; i++)
            {
                if (arrItem == intList[i])
                {
                    intList.RemoveAt(i);
                    i--;
                }
                else
                {
                    arrItem = intList[i];
                }
            }
            
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT blog_Article.*,blog_Category.* FROM 
            blog_Article INNER JOIN blog_Category ON 
            blog_Article.log_catID = blog_Category.cat_id 
            and blog_Article.log_id in ({0})  ORDER BY blog_Article.log_postTime DESC", String.Join(",", intList.ToArray())));
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateNoCatArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override IList<Article> GetArticle()
        {
            return CurrentSession.CreateCriteria(typeof(Article)).List<Article>();
        }

        public override IPagedCollection<Article> GetArticleByUserID(int startIndex, int pageSize, int userID)
        {
            IList<Article> list = CurrentSession.CreateCriteria(typeof(Article))
           .SetFirstResult(startIndex * pageSize)
           .SetMaxResults(pageSize)
           .Add(Expression.Eq("AuthorID", userID))
           .List<Article>();
            IPagedCollection<Article> lists = new PagedCollection<Article>(list);
            lists.MaxItems = Convert.ToInt32(
                CurrentSession.CreateQuery("SELECT COUNT(*) from Article as art WHERE art.AuthorID=:userID")
                .SetInt32("userID", userID).UniqueResult());

            return lists;
        }

        public override IPagedCollection<Article> GetPagedArticleByMonth(int startIndex, int pageSize, int year, int month)
        {
            IList<Article> list =CurrentSession.
                CreateQuery(@"select art from Article as art where Year(art.PostTime) = :year 
                              AND Month(art.PostTime) = :month order by art.PostTime desc")
            .SetInt32("year", year).SetInt32("month", month)
            .SetFirstResult(startIndex * pageSize)
            .SetMaxResults(pageSize)
            .List<Article>();

            IPagedCollection<Article> lists = new PagedCollection<Article>(list);
            lists.MaxItems = Convert.ToInt32(
                CurrentSession.CreateQuery(@"select COUNT(art.ArticleID) from Article as art 
                              where Year(art.PostTime) = :year 
                              AND Month(art.PostTime) = :month")
            .SetInt32("year", year).SetInt32("month", month).UniqueResult());
            return lists;
        }

        public override IPagedCollection<Article> GetPagedArticleByYear(int startIndex, int pageSize, int year)
        {
            IList<Article> list = CurrentSession.
            CreateQuery(@"select art from Article as art where Year(art.PostTime) = :year 
                          order by art.PostTime desc")
                .SetInt32("year", year)
                .SetFirstResult(startIndex * pageSize)
                .SetMaxResults(pageSize)
                .List<Article>();

            IPagedCollection<Article> lists = new PagedCollection<Article>(list);
            lists.MaxItems = Convert.ToInt32(
            CurrentSession.CreateQuery(@"select COUNT(art.ArticleID) from Article as art 
                                         where Year(art.PostTime) = :year")
            .SetInt32("year", year).UniqueResult());
            return lists;
        }

        public override IPagedCollection<Article> GetPagedArticleByDay(int startIndex, int pageSize, DateTime date)
        {
            IList<Article> list = CurrentSession.CreateQuery(
                  @"select art from Article as art where Year(art.PostTime) = :year 
                  AND Month(art.PostTime) = :month 
                  AND Day(art.PostTime) = :day order by art.PostTime desc")
                    .SetInt32("year", date.Year).SetInt32("month", date.Month).SetInt32("day", date.Day)
                    .SetFirstResult(startIndex * pageSize)
                    .SetMaxResults(pageSize)
                    .List<Article>();

            IPagedCollection<Article> lists = new PagedCollection<Article>(list);
            lists.MaxItems = Convert.ToInt32(CurrentSession.CreateQuery(
                              @"select COUNT(art.ArticleID) from Article as art 
                              where Year(art.PostTime) = :year 
                              AND Month(art.PostTime) = :month
                              AND Day(art.PostTime) = :day")
            .SetInt32("year", date.Year).SetInt32("month", date.Month).SetInt32("day", date.Day).UniqueResult());
            return lists;


        }

        public override IPagedCollection<Article> GetPagedArticleBySearch(int startIndex, int pageSize, string prefix)
        {
            StringBuilder sb = new StringBuilder();
            string[] prefixArray = prefix.Split(new char[] { ' ' });
            for (int i = 0; i < prefixArray.Length; i++)
            {
                if (i > 0)
                {
                    sb.Append("AND ");
                }
                sb.AppendFormat("log_title LIKE '%{0}%'", prefixArray[i]);
                // OR log_content0 LIKE '%"+func.checkStr(arrKeywords[i])+"%' OR log_content1 LIKE '%"+func.checkStr(arrKeywords[i])+"%')
                sb.AppendFormat(" OR log_content0 LIKE '%{0}%'", prefixArray[i]);
                sb.AppendFormat(" OR log_content1 LIKE '%{0}%'", prefixArray[i]);
            }

            //string sqllink=
            string strPageSQL2 = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) AND {2} order by log_id desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article where {2}", pageSize, startIndex, sb.ToString());

            
            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL2);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dataReader);
                    //article.Category = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                    lists.Add(article);

                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                return lists;
            }
        }

        public override Article GetArticleByID(int articleID)
        {
           return CurrentSession.Get<Article>(articleID); 
        }

        public override int InsertArticle(Article item)
        {
            CurrentSession.Save(item);

            return 1;
        }

        public override void UpdateArticle(Article item)
        {
            CurrentSession.Update(item);
            CurrentSession.Flush();
        }

        public override void UpdateArticleCount(int oldCatID, int catID, int authorID)
        {
            if (oldCatID != catID)
            {
                int oldcatCounter = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_catid={0}", oldCatID));
                ExecuteNonQuery(string.Format("UPDATE blog_Category SET cat_articleCount ={0}  WHERE cat_id={1}", oldcatCounter, oldCatID));
            }
            int catCounter = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_catid={0}", catID));
            int userCount = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_authorID={0}", authorID));
            int artCount = ExecuteScalar("select count(log_id) from blog_Article");
            ExecuteNonQuery(string.Format("UPDATE blog_Category SET cat_articleCount ={0}  WHERE cat_id={1}", catCounter, catID));
            ExecuteNonQuery(string.Format("UPDATE blog_User SET user_articleCount ={0}  WHERE user_id={1}", userCount, catID));
            ExecuteNonQuery(string.Format("UPDATE blog_Settings SET set_value0 ={0}  WHERE set_name='counterArticle'", artCount));
        }

        public override void UpdateArticleViewCount(int articleId)
        {
            ExecuteNonQuery(string.Format("update blog_article set log_viewcount=log_viewcount+1 where log_id={0}", articleId));
        }

        public override void DeleteArticle(int articleID)
        {
            CurrentSession.Delete(GetArticleByID(articleID));
            CurrentSession.Flush();
            return;
        }

        public override int GetArticleCategoryCount(int catID)
        {
            string strSql = "SELECT COUNT(log_id) FROM blog_Article";
            if (catID > 0)
            {
                strSql += " WHERE log_catID=@catID";
            }
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            db.AddInParameter(dbCommand, "catID", DbType.Int32, catID);
            return Convert.ToInt32(db.ExecuteScalar(dbCommand));
        }

        public override IList<Archive> GetArticleMonthArchive()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT Month(log_posttime) AS [Month]
	            , Year(log_posttime) AS [Year]
	            , 1 AS Day, Count(log_id) AS [Count] 
            FROM [dbo].[blog_article] 
            GROUP BY Year(log_posttime), Month(log_posttime) ORDER BY [Year] DESC, [Month] DESC");
            IList<Archive> lists = new List<Archive>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Archive archive = CommonDataProvider.PopulateArchiveFromIDataReader(dataReader);
                    lists.Add(archive);
                }
            }
            return lists;
        }

        public override IList<Article> GetAllArticleByMonth(int year, int month)
        {
            
            DbCommand dbCommand = db.GetStoredProcCommand(@"SELECT  *
            FROM blog_Article WHERE Year(log_postTime) = @year
	            AND Month(log_postTime) = @month");
            db.AddInParameter(dbCommand, "year", DbType.Int32, year);
            db.AddInParameter(dbCommand, "month", DbType.Int32, month);
            IList<Article> lists = new List<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateNoCatArticleFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override IList<ArticleLink> GetPreviousAndNextArticle(int articleID)
        {
            
            DbCommand dbCommand = db.GetStoredProcCommand("GetArticle_PreviousNext");
            db.AddInParameter(dbCommand, "articleID", DbType.Int32, articleID);
            IList<ArticleLink> lists = new List<ArticleLink>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ArticleLink article = CommonDataProvider.PopulateArticleLinkFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        #endregion


    }
}
